OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Interfacing with RDBMS stored procedures

As previously mentioned, the RUN STORED-PROCEDURE statement is the initial statement required for the execution of all stored procedures you define and initiate through Progress. It can run an RDBMS stored procedure bound by the data source, or allow you to send SQL to a MS SQL Server data source using an OpenEdge DataServer. The specific, additional keywords and elements you must include in a stored procedure or define for a send-sql-statement depend on the stored procedure characteristics including its signature, data results, and methods chosen to retrieve output to the 4GL procedure.

This section identifies and describes the syntax elements, keywords, and other requirements associated with obtaining stored procedure output from a stored procedure data request.

Example 3–3 shows a stored procedure with multiple result sets, output parameters, and a return value.

/* pcust stored procedure*/ CREATE PROCEDURE pcust (@num INT, @orders INT OUT, @states INT OUT) AS BEGIN SELECT customer.cust_num, customer.name, order_num FROM customer, order_ WHERE customer.cust_num = order_.cust_num AND customer.cust_num > @num SELECT @orders = @@rowcount SELECT cust_num, state.state FROM customer, state WHERE customer.state = state.state AND customer.cust_num > @num SELECT @states = @@rowcount END RETURN 0

/* pcust stored procedure*/ 
CREATE PROCEDURE pcust (@num INT, @orders INT OUT, @states INT OUT) AS 
BEGIN 
SELECT customer.cust_num, customer.name, order_num FROM customer, order_ 
  WHERE customer.cust_num = order_.cust_num AND customer.cust_num > @num
SELECT @orders = @@rowcount
SELECT cust_num, state.state FROM customer, state WHERE
  customer.state = state.state AND customer.cust_num > @num
SELECT @states = @@rowcount
END 
RETURN 0

This Transact-SQL code creates the stored procedure pcust and defines three parameters: num, which is an input parameter (the default parameter type), and orders and states, which are output parameters. The procedure returns values for the output parameters to the caller after processing the results of the pcust SELECT statements. You can think of output parameters as temporary fields associated with a record buffer defined by the procedure definition in the Progress schema image; that is, you can access the data in these columns using the standard notation of tablename.fieldname. (Note that although pcust is a stored procedure, it is stored as a table definition.) For example, you can access the data in the orders and states fields by specifying pcust.orders and pcust.states. All the parameters in the example have an integer data type.